﻿using System;
using System.Collections.Generic;
using System.Data;
using MergeInfo.Modal.Entity;
using MergeInfo.Modal.Interface.IDAO;
using System.Data.SqlClient;

namespace MergeInfo.Modal.DAO
{
    public class ShopDao : BaseDao, IShopDao
    {
        private const string InsertSql = @"INSERT INTO [MergeInfo].[dbo].[Shop]
           ([Id]
           ,[Name]
           ,[District]
           ,[Road]
           ,[Address]
           ,[Telephone]
           ,[Fax]
           ,[EMail]
           ,[CreateTime]
           ,[CreateUser]
           ,[UpdateTime]
           ,[UpdateUser]
           ,[Status])
     VALUES
           (@Id
           ,@Name
           ,@District
           ,@Road
           ,@Address
           ,@Telephone
           ,@Fax
           ,@EMail
           ,@CreateTime
           ,@CreateUser
           ,@UpdateTime
           ,@UpdateUser
           ,@Status)";

        private const string UpdateSql =
            @"UPDATE [MergeInfo].[dbo].[Shop]
   SET [Name] = @Name
      ,[District] = @District
      ,[Road] = @Road
      ,[Address] = @Address
      ,[Telephone] = @Telephone
      ,[Fax] = @Fax
      ,[EMail] = @EMail
      ,[CreateTime] = @CreateTime
      ,[CreateUser] = @CreateUser
      ,[UpdateTime] = @UpdateTime
      ,[UpdateUser] = @UpdateUser
      ,[Status] = @Status
 WHERE [Id] = @Id";

        private const string SelectSql = @"SELECT [Id]
      ,[Name]
      ,[Address]
      ,[Telephone]
      ,[Fax]
      ,[EMail]
      ,[CreateTime]
      ,[UpdateTime]
      ,[DistrictId]
      ,[DistrictName]
      ,[RoadId]
      ,[RoadName]
      ,[StatusId]
      ,[StatusName]
      ,[CreateMemberAccount]
      ,[CreateMemberName]
      ,[UpdateMemberAccount]
      ,[UpdateMemberName]
  FROM [MergeInfo].[dbo].[vShop]";

        public void Create(ShopEntity shopEntity)
        {
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = InsertSql };
            SqlCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.UniqueIdentifier));
            SqlCommand.Parameters["@Id"].Value = shopEntity.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Name"].Value = shopEntity.Name;

            SqlCommand.Parameters.Add(new SqlParameter("@District", SqlDbType.Int));
            SqlCommand.Parameters["@District"].Value = shopEntity.District.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@Road", SqlDbType.Int));
            SqlCommand.Parameters["@Road"].Value = shopEntity.Road.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 100));
            SqlCommand.Parameters["@Address"].Value = shopEntity.Address;

            SqlCommand.Parameters.Add(new SqlParameter("@Telephone", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Telephone"].Value = shopEntity.Telephone;

            SqlCommand.Parameters.Add(new SqlParameter("@Fax", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Fax"].Value = shopEntity.Fax;

            SqlCommand.Parameters.Add(new SqlParameter("@EMail", SqlDbType.NVarChar, 100));
            SqlCommand.Parameters["@EMail"].Value = shopEntity.EMail;

            SqlCommand.Parameters.Add(new SqlParameter("@CreateTime", SqlDbType.DateTime));
            SqlCommand.Parameters["@CreateTime"].Value = shopEntity.CreateTime;

            SqlCommand.Parameters.Add(new SqlParameter("@CreateUser", SqlDbType.NVarChar, 100));
            SqlCommand.Parameters["@CreateUser"].Value = shopEntity.CreateUser.Account;

            SqlCommand.Parameters.Add(new SqlParameter("@UpdateTime", SqlDbType.DateTime));
            if (shopEntity.UpdateTime != null)
                SqlCommand.Parameters["@UpdateTime"].Value = shopEntity.UpdateTime;
            else
                SqlCommand.Parameters["@UpdateTime"].Value = DBNull.Value;

            SqlCommand.Parameters.Add(new SqlParameter("@UpdateUser", SqlDbType.NVarChar, 100));

            if (shopEntity.UpdateUser != null)
                SqlCommand.Parameters["@UpdateUser"].Value = shopEntity.UpdateUser.Account;
            else
                SqlCommand.Parameters["@UpdateUser"].Value = DBNull.Value;

            SqlCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
            SqlCommand.Parameters["@Status"].Value = shopEntity.Status.Id;

            Submit();
        }

        public void Update(ShopEntity shopEntity)
        {
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = UpdateSql };
            SqlCommand.Parameters.Add(new SqlParameter("@Id", SqlDbType.UniqueIdentifier));
            SqlCommand.Parameters["@Id"].Value = shopEntity.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Name"].Value = shopEntity.Name;

            SqlCommand.Parameters.Add(new SqlParameter("@District", SqlDbType.Int));
            SqlCommand.Parameters["@District"].Value = shopEntity.District.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@Road", SqlDbType.Int));
            SqlCommand.Parameters["@Road"].Value = shopEntity.Road.Id;

            SqlCommand.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 100));
            SqlCommand.Parameters["@Address"].Value = shopEntity.Address;

            SqlCommand.Parameters.Add(new SqlParameter("@Telephone", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Telephone"].Value = shopEntity.Telephone;

            SqlCommand.Parameters.Add(new SqlParameter("@Fax", SqlDbType.NVarChar, 50));
            SqlCommand.Parameters["@Fax"].Value = shopEntity.Fax;

            SqlCommand.Parameters.Add(new SqlParameter("@EMail", SqlDbType.NVarChar, 100));
            SqlCommand.Parameters["@EMail"].Value = shopEntity.EMail;

            SqlCommand.Parameters.Add(new SqlParameter("@CreateTime", SqlDbType.DateTime));
            SqlCommand.Parameters["@CreateTime"].Value = shopEntity.CreateTime;

            SqlCommand.Parameters.Add(new SqlParameter("@CreateUser", SqlDbType.NVarChar, 100));
            SqlCommand.Parameters["@CreateUser"].Value = shopEntity.CreateUser.Account;

            SqlCommand.Parameters.Add(new SqlParameter("@UpdateTime", SqlDbType.DateTime));
            if (shopEntity.UpdateTime != null)
                SqlCommand.Parameters["@UpdateTime"].Value = shopEntity.UpdateTime;
            else
                SqlCommand.Parameters["@UpdateTime"].Value = DBNull.Value;

            SqlCommand.Parameters.Add(new SqlParameter("@UpdateUser", SqlDbType.NVarChar, 100));

            if (shopEntity.UpdateUser != null)
                SqlCommand.Parameters["@UpdateUser"].Value = shopEntity.UpdateUser.Account;
            else
                SqlCommand.Parameters["@UpdateUser"].Value = DBNull.Value;

            SqlCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.Int));
            SqlCommand.Parameters["@Status"].Value = shopEntity.Status.Id;

            Submit();
        }

        public IList<ShopEntity> Select(string name)
        {
            const string sql = SelectSql + " where [Name]=@Name";
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = sql };
            SqlCommand.Parameters.Add(new SqlParameter("@Name", name));
            SqlCommand.Connection.Open();
            SqlDataReader sqlDataReader = SqlCommand.ExecuteReader();
            IList<ShopEntity> shopEntities = AssemblyList(sqlDataReader);
            SqlCommand.Connection.Close();
            return shopEntities;
        }

        public IList<ShopEntity> Select(Guid id)
        {
            const string sql = SelectSql + " where [Id]=@Id";
            SqlCommand = new SqlCommand { Connection = SqlConnection, CommandText = sql };
            SqlCommand.Parameters.Add(new SqlParameter("@Id", id));
            SqlCommand.Connection.Open();
            SqlDataReader sqlDataReader = SqlCommand.ExecuteReader();
            IList<ShopEntity> shopEntities = AssemblyList(sqlDataReader);
            SqlCommand.Connection.Close();
            return shopEntities;
        }

        private static IList<ShopEntity> AssemblyList(SqlDataReader sqlDataReader)
        {
            IList<ShopEntity> shopEntities = new List<ShopEntity>();
            while (sqlDataReader.Read())
            {
                var shopEntity = new ShopEntity
                {
                    Id = new Guid(sqlDataReader["Id"].ToString()),
                    Name = sqlDataReader["Name"].ToString(),
                    Address = sqlDataReader["Address"].ToString(),
                    Telephone = sqlDataReader["Telephone"].ToString(),
                    Fax = sqlDataReader["Fax"].ToString(),
                    EMail = sqlDataReader["EMail"].ToString(),
                    CreateTime = Convert.ToDateTime(sqlDataReader["CreateTime"].ToString()),
                    District = new DataDictionaryEntity
                    {
                        Id = Convert.ToInt32(sqlDataReader["DistrictId"].ToString()),
                        Name = sqlDataReader["DistrictName"].ToString()
                    },
                    Road = new DataDictionaryEntity
                    {
                        Id = Convert.ToInt32(sqlDataReader["RoadId"].ToString()),
                        Name = sqlDataReader["RoadName"].ToString()
                    },
                    Status = new DataDictionaryEntity
                    {
                        Id = Convert.ToInt32(sqlDataReader["StatusId"].ToString()),
                        Name = sqlDataReader["StatusName"].ToString()
                    },
                    CreateUser = new ShopMemberEntity
                    {
                        Account = sqlDataReader["CreateMemberAccount"].ToString(),
                        Name = sqlDataReader["CreateMemberName"].ToString()
                    },

                };
                if (!string.IsNullOrEmpty(sqlDataReader["UpdateTime"].ToString()))
                    shopEntity.UpdateTime = Convert.ToDateTime(sqlDataReader["UpdateTime"].ToString());

                if (!string.IsNullOrEmpty(sqlDataReader["UpdateMemberAccount"].ToString()))
                    shopEntity.UpdateUser = new ShopMemberEntity
                    {
                        Account = sqlDataReader["UpdateMemberAccount"].ToString(),
                        Name = sqlDataReader["UpdateMemberName"].ToString()
                    };
                shopEntities.Add(shopEntity);
                sqlDataReader.NextResult();
            }
            return shopEntities;
        }
    }
}
